package com.intellekt.ecert.exportacion.dao;

import static com.intellekt.ecert.domain.DomainUtils.parseStringToBigDecimal;

import java.sql.Blob;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.GregorianCalendar;
import java.util.List;

import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;

import org.apache.log4j.Logger;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSAddressType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSAuthenticationType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSClauseType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSConsignmentType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSCountryType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSEventType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSExchangedDocumentType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSLocationType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSNoteType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSPackageType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSPartyType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSPersonType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSQualificationType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSReferencedDocumentType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSShippingMarksType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSTemperatureType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSTradeLineItemType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSTransportEquipmentType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSTransportMeansType;
import un.unece.uncefact.data.standard.reusableaggregatebusinessinformationentity._10.SPSTransportMovementType;
import un.unece.uncefact.data.standard.unqualifieddatatype._10.MeasureType;
import un.unece.uncefact.data.standard.unqualifieddatatype._10.QuantityType;
import un.unece.uncefact.data.standard.unqualifieddatatype._10.TextType;

import com.intellekt.ecert.domain.JdbcDao;
import com.intellekt.ecert.exportacion.vo.CertificadoVO;

public class ExportacionDaoImpl extends JdbcDao implements ExportacionDao {

	private static final Logger log = Logger.getLogger(ExportacionDaoImpl.class);

	private String qryCertificatesByInfo;
	private String qryCertificatesById;
	private String qryItemsById;
	private String qryPdf;
	private String qryPdfBlob;

	private static ParameterizedRowMapper<CertificadoVO> CERT_MAPPER = new ParameterizedRowMapper<CertificadoVO>() {
		public CertificadoVO mapRow(ResultSet rs, int rowNum) throws SQLException {
			CertificadoVO obj = new CertificadoVO();
			SPSExchangedDocumentType exdoc = new SPSExchangedDocumentType();

			SPSConsignmentType cons = new SPSConsignmentType();

			TextType tt = new TextType();

			tt.setLanguageID("es");
			// tt.setValue(rs.getString("SPSEXCHANGEDDOCUMENT_NAME"));
			tt.setValue(rs.getString("DESC_MODALIDAD"));
			exdoc.getName().add(tt);

			exdoc.setID(rs.getString("SPSEXCHANGEDDOCUMENT_ID"));

			tt = new TextType();
			tt.setLanguageID("es");
			tt.setValue(rs.getString("ISSUERSPSPARTY_NAME"));
			exdoc.getDescription().add(tt);

			exdoc.setTypeCode(rs.getString("TYPECODE"));
			exdoc.setStatusCode(rs.getString("STATUSCODE"));

			try {
				GregorianCalendar gc = new GregorianCalendar();
				Date dt = rs.getDate("ISSUEDATETIME");
				gc.setTime(dt);
				exdoc.setIssueDateTime(DatatypeFactory.newInstance().newXMLGregorianCalendar(gc));
			} catch (DatatypeConfigurationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			SPSPartyType partyType = new SPSPartyType();
			partyType.setID("DGSV");
			partyType.setName(rs.getString("ISSUERSPSPARTY_NAME"));
			partyType.setRoleCode("PQ");
			exdoc.setIssuerSPSParty(partyType);

			exdoc.getRecipientSPSParty().add(new SPSPartyType());

			SPSNoteType note = new SPSNoteType();
			note.setSubject(rs.getString("INCLUDEDSPSNOTE_SUBJECT"));
			note.getContent().add(rs.getString("INCLUDEDSPSNOTE_CONTENT"));
			exdoc.getIncludedSPSNote().add(note);

			SPSReferencedDocumentType ref = new SPSReferencedDocumentType();
			ref.setTypeCode(rs.getString("TYPECODE"));
			ref.setRelationshipTypeCode(rs.getString("STATUSCODE"));
			ref.setID(rs.getString("SPSEXCHANGEDDOCUMENT_ID"));

			try {
				GregorianCalendar gc = new GregorianCalendar();
				Date dt = rs.getDate("ISSUEDATETIME");
				gc.setTime(dt);
				ref.setIssueDateTime(DatatypeFactory.newInstance().newXMLGregorianCalendar(gc));
			} catch (DatatypeConfigurationException e) {
				e.printStackTrace();
			}
			ref.setInformation("");
			exdoc.getReferenceSPSReferencedDocument().add(ref);

			SPSAuthenticationType authType = new SPSAuthenticationType();
			try {
				GregorianCalendar gc = new GregorianCalendar();
				Date dt = rs.getDate("ACTUALDATETIME");
				gc.setTime(dt);
				authType.setActualDateTime(DatatypeFactory.newInstance().newXMLGregorianCalendar(gc));
			} catch (DatatypeConfigurationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			SPSLocationType locType = new SPSLocationType();
			locType.getName().add(rs.getString("ISSUESPSLOCATION_NAME"));
			authType.setIssueSPSLocation(locType);

			partyType = new SPSPartyType();
			partyType.setName(rs.getString("PROVIDERSPSPARTY_NAME"));

			SPSPersonType person = new SPSPersonType();
			person.setName(rs.getString("SPECIFIEDSPSPERSON_NAME"));

			SPSQualificationType qual = new SPSQualificationType();
			qual.getName().add(rs.getString("ATTAINEDSPSQUALIFICATION_NAME"));
			qual.setAbbreviatedName(rs.getString("ATTAINED_ABBREVIATEDNAME"));
			person.getAttainedSPSQualification().add(qual);
			partyType.setSpecifiedSPSPerson(person);
			authType.setProviderSPSParty(partyType);

			// exdoc.getSignatorySPSAuthentication().add(authType);

			SPSClauseType clause = new SPSClauseType();
			clause.getContent().add(rs.getString("INCLUDEDSPSCLAUSE_CONTENT"));
			authType.getIncludedSPSClause().add(clause);

			exdoc.getSignatorySPSAuthentication().add(authType);

			/** END OF EXCHANGEDDOCUMENT **/

			partyType = new SPSPartyType();
			partyType.setName(rs.getString("CONSIGNOR_NAME"));

			SPSAddressType address = new SPSAddressType();
			address.setPostcodeCode(rs.getString("CONSIGNORPOSTCODE"));
			address.setLineOne(rs.getString("CONSIGNORLINEONE"));
			address.setCityName(rs.getString("CONSIGNORCITYNAME"));
			address.setCountryID(rs.getString("CONSIGNORCOUNTRYID"));
			address.setCountryName(rs.getString("CONSIGNORCOUNTRYNAME"));
			address.setCountrySubDivisionID("");

			partyType.setSpecifiedSPSAddress(address);

			cons.setConsignorSPSParty(partyType);

			partyType = new SPSPartyType();
			partyType.setName(rs.getString("CONSIGNEE_NAME"));

			address = new SPSAddressType();
			address.setPostcodeCode("");
			address.setLineOne(rs.getString("CONSIGNEE_LINEONE"));
			address.setCityName(rs.getString("CONSIGNEECITYNAME") == null ? "" : rs.getString("CONSIGNEECITYNAME"));
			address.setCountryID(rs.getString("CONSIGNEECOUNTRYID"));
			address.setCountryName(rs.getString("CONSIGNEECOUNTRYNAME"));

			partyType.setSpecifiedSPSAddress(address);
			cons.setConsigneeSPSParty(partyType);

			SPSCountryType country = new SPSCountryType();
			country.setID(rs.getString("EXPORTSPSCOUNTRY_ID"));
			country.getName().add(rs.getString("EXPORTSPSCOUNTRY_NAME"));

			cons.setExportSPSCountry(country);

			country = new SPSCountryType();
			country.setID(rs.getString("IMPORTSPSCOUNTRY_ID"));
			country.getName().add(rs.getString("IMPORTSPSCOUNTRY_NAME"));

			cons.setImportSPSCountry(country);

			locType = new SPSLocationType();
			locType.getName().add(rs.getString("UNLOADINGBASEPORTSPSLOCATION"));

			cons.setUnloadingBaseportSPSLocation(locType);

			SPSEventType event = new SPSEventType();
			locType = new SPSLocationType();
			locType.getName().add(rs.getString("OCCURRENCESPSLOCATION_NAME"));
			event.setOccurrenceSPSLocation(locType);

			cons.setExaminationSPSEvent(event);

			SPSTransportMovementType movement = new SPSTransportMovementType();
			SPSTransportMeansType means = new SPSTransportMeansType();

			movement.setModeCode(rs.getString("TRANSPORTMOVEMENT_CODE") == null ? "" : rs.getString("TRANSPORTMOVEMENT_CODE"));
			means.setName(rs.getString("TRANSPORTMOVEMENT_MODECODE"));
			movement.setUsedSPSTransportMeans(means);

			cons.getMainCarriageSPSTransportMovement().add(movement);

			// rs.getString("LOADINGBASEPORTSPSLOCATION")

			obj.setSPSExchangedDocument(exdoc);
			obj.setSPSConsignment(cons);

			return obj;
		}
	};

	private static ParameterizedRowMapper<SPSTradeLineItemType> ITEM_MAPPER = new ParameterizedRowMapper<SPSTradeLineItemType>() {
		public SPSTradeLineItemType mapRow(ResultSet rs, int rowNum) throws SQLException {
			SPSTradeLineItemType obj = new SPSTradeLineItemType();
			SPSNoteType noteType = new SPSNoteType();
			SPSPackageType packageType = new SPSPackageType();
			QuantityType quantType = new QuantityType();
			MeasureType measureType = new MeasureType();
			SPSShippingMarksType marksType = new SPSShippingMarksType();
			SPSCountryType countryType = new SPSCountryType();
			SPSTransportEquipmentType quip = new SPSTransportEquipmentType();
			SPSTemperatureType temp = new SPSTemperatureType();
			obj.setSequenceNumeric(rs.getString("SEQUENCENUMERIC"));
			obj.getDescription().add(rs.getString("DESCRIPTION"));
			obj.getCommonName().add(rs.getString("COMMONNAME"));
			obj.getScientificName().add(rs.getString("SCIENTIFICNAME"));

			noteType.setSubject(rs.getString("ADDITIONALINFO_SUBJECT") == null ? "" : rs.getString("ADDITIONALINFO_SUBJECT"));
			noteType.getContent().add(rs.getString("ADDITIONALINFO_CONTENT"));
			obj.getAdditionalInformationSPSNote().add(noteType);

			packageType.setLevelCode(rs.getString("PACKAGE_LEVELCODE"));

			try {
				quantType.setValue(parseStringToBigDecimal("0"/*
															 * rs.getString(
															 * "ITEMQUANTITY")
															 */));
				quantType.setUnitCode(rs.getString("UNITCODE"));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			packageType.setItemQuantity(quantType);

			measureType.setValue(/* rs.getString("PACKAGE_NOMINALVOLUMEMEASURE") */rs.getString("ITEMQUANTITY"));
			packageType.setNominalGrossVolumeMeasure(measureType);

			marksType.setMarking(rs.getString("PHYSICALSPSSHIPPINGMARK"));
			packageType.getPhysicalSPSShippingMarks().add(marksType);

			temp.setTypeCode("CEL");
			temp.setValueMeasure(rs.getString("DESC_TEMP_TRATAMIENTO"));
			quip.getSettingSPSTemperature().add(temp);

			obj.getAssociatedSPSTransportEquipment().add(quip);

			obj.getPhysicalSPSPackage().add(packageType);

			countryType.setID(rs.getString("ORIGINSPSCOUNTRY_ID"));
			countryType.getName().add(rs.getString("ORIGINSPSCOUNTRY_NAME"));

			obj.getOriginSPSCountry().add(countryType);

			SPSLocationType loc = new SPSLocationType();
			loc.setID(rs.getString("ID_ORIGIN_LOCATION"));
			loc.getName().add(rs.getString("ORIGIN_LOCATION"));
			obj.getOriginSPSLocation().add(loc);

			return obj;
		}
	};

	@Override
	public List<CertificadoVO> getCertificatesByInfo(String from, String to, String issueCountry) {
		if (log.isDebugEnabled()) {
			log.debug("getCertificatesByInfo" + from + to);
		}
		String params[] = { from, to, issueCountry };

		return jdbcTemplateVU.query(qryCertificatesByInfo, params, CERT_MAPPER);
	}

	@Override
	public CertificadoVO getCertificatesById(String idCert) {
		if (log.isDebugEnabled()) {
			log.debug("getCertificatesById");
		}
		String params[] = { idCert };
		try {
			return jdbcTemplateVU.queryForObject(qryCertificatesById, params, CERT_MAPPER);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@Override
	public boolean getPdf(String idCert) {
		if (log.isDebugEnabled()) {
			log.debug("getPdf");
		}
		String params[] = { idCert };

		Integer x = jdbcTemplateVU.queryForObject(qryPdf, params, Integer.class);

		//if (x==1) {
			return true;
		//} else {
		//	return false;
		//}
	}

	@Override
	public byte[] getPdfStream(String idCert) {
		if (log.isDebugEnabled()) {
			log.debug("getPdf");
		}
		String params[] = { idCert };
		final Blob blob;
		blob = jdbcTemplateVU.queryForObject(qryPdfBlob, params, Blob.class);
		
		try {
			return blob.getBytes(0, (int) blob.length());
		} catch (SQLException e) {
			return null;
		}
	}
	
	@Override
	public List<SPSTradeLineItemType> getCertificateItems(String id) {
		if (log.isDebugEnabled()) {
			log.debug("getCertificateItems");
		}
		String params[] = { id };

		return jdbcTemplateVU.query(qryItemsById, params, ITEM_MAPPER);
	}

	public void setQryCertificatesByInfo(String qryCertificatesByInfo) {
		this.qryCertificatesByInfo = qryCertificatesByInfo;
	}

	public void setQryCertificatesById(String qryCertificatesById) {
		this.qryCertificatesById = qryCertificatesById;
	}

	public void setQryItemsById(String qryItemsById) {
		this.qryItemsById = qryItemsById;
	}

	public void setQryPdf(String qryPdf) {
		this.qryPdf = qryPdf;
	}

	public void setQryPdfBlob(String qryPdfBlob) {
		this.qryPdfBlob = qryPdfBlob;
	}

	

}
